﻿<%@ WebHandler Language="C#" Class="MultiProductToolsInfoReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;

/**
 * Description: 多零件工装工具查询报表类
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.11.25
 **/

public class MultiProductToolsInfoReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT DISTINCT T.* 
FROM (
    SELECT PB.PRODUCTNAME,P.PRODUCTREVISION,
           P.DESCRIPTION productdesc,
           WFT.WORKFLOWSTEPNAME,
           CASE
             WHEN CT.CUTTOOLID IS NOT NULL THEN CT.CUTTOOLNAME
             WHEN MT.MEASURETOOLID IS NOT NULL THEN MT.MEASURETOOLNAME
             WHEN ST.SPECIALTOOLID IS NOT NULL THEN ST.SPECIALTOOLNAME
           END TOOLNAME,
           CASE
             WHEN CT.CUTTOOLID IS NOT NULL THEN CT.DESCRIPTION
             WHEN MT.MEASURETOOLID IS NOT NULL THEN MT.DESCRIPTION
             WHEN ST.SPECIALTOOLID IS NOT NULL THEN ST.DESCRIPTION
           END DESCRIPTION,
           CASE
             WHEN CT.CUTTOOLID IS NOT NULL THEN CT.DRAWINGNO
             WHEN MT.MEASURETOOLID IS NOT NULL THEN MT.DRAWINGNO
             WHEN ST.SPECIALTOOLID IS NOT NULL THEN ST.DRAWINGNO
           END DRAWINGNO,
           CASE
             WHEN CT.CUTTOOLID IS NOT NULL THEN '刀具'
             WHEN MT.MEASURETOOLID IS NOT NULL THEN '量具'
             WHEN ST.SPECIALTOOLID IS NOT NULL THEN '夹模具'
           END TYPENAME,
           WFST.QTY,
           WFT.SEQUENCE
    FROM PRODUCT P
    LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID=P.PRODUCTBASEID
    LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID=P.WORKFLOWID
    LEFT JOIN WORKFLOWBASE WFB ON WFB.WORKFLOWBASEID=WF.WORKFLOWBASEID
    LEFT JOIN WORKFLOWSTEP WFT ON WFT.WORKFLOWID=WF.WORKFLOWID
    INNER JOIN WORKFLOWSTEPTOOLS WFST ON WFST.WORKFLOWSTEPID=WFT.WORKFLOWSTEPID
    LEFT JOIN CUTTOOL CT ON CT.CUTTOOLID=WFST.TOOLS
    LEFT JOIN MEASURETOOL MT ON MT.MEASURETOOLID=WFST.TOOLS
    LEFT JOIN SPECIALTOOL ST ON ST.SPECIALTOOLID=WFST.TOOLS
    WHERE PB.PRODUCTNAME IN ({0})
) T
ORDER BY T.PRODUCTNAME,T.SEQUENCE,T.TOOLNAME
";
        if (jObject["productnamelist"] != null && jObject["productnamelist"].ToString() != "")
        {
            JArray productnamelist = JArray.Parse(jObject["productnamelist"].ToString());
            string list = "";
            for (int i = 0; i < productnamelist.Count; i++)
            {
                list += string.Format("'{0}',", productnamelist[i].ToString());
            }
            list = list.TrimEnd(',');
            sql = string.Format(sql, list);
        }
        return sql;
    }

    //查询
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns.Remove("SEQUENCE");
        dt.Columns["PRODUCTNAME"].Caption = "零件图号";
        dt.Columns["PRODUCTREVISION"].Caption = "零件版本";
        dt.Columns["PRODUCTDESC"].Caption = "零件名称";
        dt.Columns["WORKFLOWSTEPNAME"].Caption = "工序";
        dt.Columns["TOOLNAME"].Caption = "工具编号";
        dt.Columns["DESCRIPTION"].Caption = "工具名称";
        dt.Columns["DRAWINGNO"].Caption = "规格/图号";
        dt.Columns["TYPENAME"].Caption = "类型";
        dt.Columns["QTY"].Caption = "需求数量";
        ExportHelper.ExportExcel(dt, "多零件工装工具查询报表类");
    }
}